package us.gaaoc.framework.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.faces.context.FacesContext;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;

import us.gaaoc.framework.businessmodel.Filing;
import us.gaaoc.framework.model.CaseRecord;
import us.gaaoc.framework.model.CaseRecordStatus;
import us.gaaoc.framework.model.CaseRecordType;
import us.gaaoc.framework.model.Court;
import us.gaaoc.framework.model.LocalCaseCategory;
import us.gaaoc.framework.model.LocalCaseDivision;
import us.gaaoc.framework.model.LocalCaseFilingType;
import us.gaaoc.framework.model.LocalCaseType;
import us.gaaoc.framework.model.util.CaseRecordsCountPerDay;
import us.gaaoc.framework.model.util.CaseRecordsCountPerMonth;
import us.gaaoc.framework.model.util.DismissedCases;
import us.gaaoc.framework.model.util.DismissedCasesPerMonth;
import us.gaaoc.framework.util.validators.NameValidator;

/**
 * Home object for domain model class CaseRecord.
 * 
 * @see us.gaaoc.framework.model.CaseRecord
 * @author Hibernate Tools
 */
@SuppressWarnings({"unused", "unchecked"})
public class CaseRecordDAOHibernate extends
	GenericHibernateDAO<CaseRecord, String, CaseRecordDAO> implements
	CaseRecordDAO {

	Logger LOG = Logger.getLogger(CaseRecordDAOHibernate.class);

	private String courtUuid;
	
	protected CaseRecordDAOHibernate(String courtUuid) {
		this.courtUuid = courtUuid;
	}
	
	// changed
	public List<CaseRecord> findAllCaseRecordByStatus(int statusCode) {

		return (getSession().createCriteria(CaseRecord.class).add(Restrictions
				.eq("status.code", 1))).add(
						Restrictions.eq("courtUuid", courtUuid)).add(
						Restrictions.eq("clearFilingFlag", false)).list();
	}

	// changed
	public List<CaseRecord> findAllCaseRecordsByCaseTypeAndStatus(
			int caseTypeCode, int statusCode1, int statusCode2) {
		Query q = getSession()
				.createQuery(
						"from CaseRecord c where c.caseType = ? and c.clearFilingFlag = false " +
						"and (statusCode = ? or statusCode = ?) and c.courtUuid = :courtUuid order by c.instantiationDateTime desc");
		q.setInteger(0, caseTypeCode);
		q.setInteger(1, statusCode1);
		q.setInteger(2, statusCode2);
		q.setParameter("courtUuid", courtUuid);
		return q.list();
	}
	
	// changed
	public List<CaseRecord> findAllCaseRecordByCaseType(int caseTypeCode) {
		Query q = getSession()
		.createQuery(
		"from CaseRecord c where c.caseType = ? and c.clearFilingFlag = false and c.courtUuid = :courtUuid order by c.instantiationDateTime desc");
		q.setInteger(0, caseTypeCode);
		q.setParameter("courtUuid", courtUuid);
		return q.list();
	}
	
	// changed
	public List<CaseRecord> findAllCaseRecordByTransmissionError() {
		return (getSession().createCriteria(CaseRecord.class).add(Restrictions
				.eq("communicationFailed", true))).add(
						Restrictions.eq("courtUuid", courtUuid)).add(
						Restrictions.eq("clearFilingFlag", false)).list();
	}

	public void updateCaseRecordForClearCase(String filingUuid, boolean clearInt) {
		//CaseRecord cr = this.findById(filingUuid, false);
		CaseRecord cr = this.findById(filingUuid);
		cr.setClearFilingFlag(clearInt);
		this.makePersistent(cr);
	}

	// changed
	public List<CaseRecord> findAllClearedCaseRecords(boolean flag) {
		List<CaseRecord> filingList = new ArrayList<CaseRecord>();
		filingList = getSession().createCriteria(CaseRecord.class).add(
				Restrictions.eq("courtUuid", courtUuid)).add(
				Restrictions.eq("clearFilingFlag", flag)).list();
		return filingList;
	}
	
	// changed
	public List<Filing> findCaseFilingsByTypeAndStatus(int typeCode,
			int statusCode) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.status.code = ? "
		+ "AND c.caseType.code = ? "	
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, statusCode);
		q.setInteger(1, typeCode);
		q.setParameter("courtUuid", courtUuid);
		List list = q.list();
		Iterator i = list.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];

			Filing f = new Filing(uuid, caseTrackingID, status, caseType, instantiationDate,defendantName,eventType,eventDate);
			
			filings.add(f);
		}
		return filings;

	} 
	
	// changed
	/**
	 * Method to query paged results of case filings by type and status.
	 * 
	 * @param typeCode
	 * @param statusCode
	 * @param pageIndex
	 * @param limit
	 * @return List<Filing>
	 */
	public List<Filing> findCaseFilingsByTypeAndStatusPaged(int typeCode,
			int statusCode, int pageIndex, int limit) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.status.code = ? "
		+ "AND c.caseType.code = ? "	
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, statusCode);
		q.setInteger(1, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		// Set page to query
		q.setFirstResult(pageIndex);
		// Set max results for page
		q.setMaxResults(limit);
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];

			Filing f = new Filing(uuid, caseTrackingID, status, caseType, instantiationDate,defendantName,eventType,eventDate);
			filings.add(f);
		}
		return filings;

	} 
	
	// changed
	/**
	 * Method to determine how many rows are possible to be returned by a query 
	 * for case filings by type and status.
	 * 
	 * @param typeCode
	 * @param statusCode
	 * @param pageIndex
	 * @param limit
	 * @return int Count of number of rows
	 */
	public int findNumberOfCaseFilingsByTypeAndStatus(int typeCode,
			int statusCode) {
		String sql = "SELECT COUNT(*)FROM caseRecord c WHERE c.clearFilingFlag" +
		" = false and  c.statusCode = ? and c.caseTypeCode = ? and " +
		"c.deletedFilingFlag = false and c.archivedFlag = false and c.courtUuid = :courtUuid";  
		Query q = getSession().createSQLQuery(sql);
		q.setInteger(0, statusCode);
		q.setInteger(1, typeCode);
		q.setParameter("courtUuid", courtUuid);
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		LOG.info("The row number of cases of status "+statusCode+" is "+resultSetSize);
		return resultSetSize;

	} 
	
	// changed
	public List<Filing> findCaseFilingsByStatus(int statusCode) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.status.code = ? " 	
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setInteger(0, statusCode);
		q.setParameter("courtUuid", courtUuid);
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	}  

	// changed
	public List<Filing> findCaseFilingsByType(int typeCode) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.caseType.code = ? "	
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;

	}  

	// changed
	// TODO Needs a paged version of this query as some court clear a large amount of filings.
	public List<Filing> findClearedCaseFilingsByType(int typeCode) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.caseType.code = ? "	
		+ "AND c.clearFilingFlag = true " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();		
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	}  

	// changed
	public List<Filing> findClearedCaseFilings() {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.clearFilingFlag = true " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
	
		q.setParameter("courtUuid", courtUuid);
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();		
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	}  

	// changed
	public List<Filing> findErrorFilings() {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = 7"
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	} 

	public void updateCaseRecordForDeletedFlag(String uuid, boolean isDeleted) {
		//CaseRecord cr = this.findById(uuid, true);
		CaseRecord cr = this.findById(uuid);
		cr.setDeletedFilingFlag(isDeleted);
		this.makePersistent(cr);

	}

	public List<CaseRecord> findCaseRecordByDocketID(String docketID,String court) {
		Query q = this.getSession().createQuery(
				"from CaseRecord cr where cr.caseTrackingId = ? and "
				+ "cr.deletedFilingFlag = false and cr.courtUuid = ?");
		//TODO: filter by court
		//TODO: return a list.
		q.setString(0, docketID);
		q.setString(1, court);
		return q.list();
	}

	public void updateImportedCaseRecord(String uuid, String docketID,
			CaseRecordStatus status) {
		//CaseRecord c = this.findById(uuid, true);
		CaseRecord c = this.findById(uuid);
		c.setCaseTrackingId(docketID);
		c.setStatus(status);
		//TODO: filter by court
		//TODO: return a list.
		this.makePersistent(c);

	}

	public void updateRejectedCaseRecord(String uuid, String reason,
			CaseRecordStatus status) {
		//CaseRecord c = this.findById(uuid, true);
		CaseRecord c = this.findById(uuid);
		c.setRejectionReason(reason);
		c.setStatus(status);
		this.makePersistent(c);

	}
    
    public void updateCaseRecordCourtTrackingID(String uuid, String docketID) { 
        //CaseRecord c = this.findById(uuid, true); 
        CaseRecord c = this.findById(uuid); 
        c.setCaseTrackingId(docketID); 
        this.makePersistent(c); 
    } 
	
	public List<Filing> findProgressingFilings() {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.status.code = 7 OR c.status.code = 8)" 
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	}

	public List<Filing> findExistingFilings() {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.status.code = 15" 
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	}

	public List<Filing> findAttorneySideStatusFilings() {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.status.code = 10 OR c.status.code = 11 or c.status.code = 3)" 
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;

	}

	public List<Filing> findCourtSideStatusFilings() {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.status.code = 1 OR c.status.code = 2 OR c.status.code = 12)" 
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	}

	public CaseRecord findCaseRecordByExternalCaseRecordUuidAndExternalCaseTypeCode(
			String externalCaseRecordUuid, int externalCaseTypeCode) {
		String sql = "SELECT cr.uuid from caseRecord cr where cr.externalCaseRecordUuid ="
			+ externalCaseRecordUuid
			+ " and cr.externalCaseTypeCode ="
			+ new Integer(externalCaseTypeCode);
		Query q = this.getSession().createSQLQuery(sql);
		LOG.info("The query has returned ....");
		String uuid = (String) q.uniqueResult();
		LOG.info("the returned uuid string is ..." + uuid);
		if (uuid == null) {
			CaseRecord caseRecord = new CaseRecord();
			caseRecord = null;
			return caseRecord;
		} else {
			LOG.info("Getting the case record of uuid ..." + uuid);
			// String sql1 = "SELECT * FROM caseRecord cr where cr.uuid ="+uuid;
			// Query q1 = this.getSession().createSQLQuery(sql1);

			Query q1 = this.getSession().createQuery(
			"from CaseRecord cr where cr.uuid = ? ");
			q1.setString(0, uuid);
			// CaseRecord caseRecord = new CaseRecord();
			// caseRecord = (CaseRecord)q1.uniqueResult();
			// LOG.info("the returned case record is of uuid
			// ...."+caseRecord.getUuid());
			return (CaseRecord) q1.uniqueResult();
		}
	}

	public boolean isExternalCaseRecordUuidAndExternalCaseTypeCode(
			String externalCaseRecordUuid, int externalCaseTypeCode) {
		String sql = "SELECT cr.uuid from caseRecord cr where cr.externalCaseRecordUuid ="
			+ externalCaseRecordUuid
			+ " and cr.externalCaseTypeCode ="
			+ new Integer(externalCaseTypeCode);
		Query q = this.getSession().createSQLQuery(sql);
		LOG.info("The query has returned ....");
		String uuid = (String) q.uniqueResult();
		LOG.info("the returned uuid string is ..." + uuid);
		if (uuid == null) {
			return false;
		} else {
			return true;
		}
		/*
		 * Query q1 = this.getSession().createQuery("from caseRecord cr where
		 * cr.uuid = ?"); q1.setString(0, uuid);
		 * if((CaseRecord)q1.uniqueResult()!=null){ return true; }else{ return
		 * false; }
		 */
	}

	public void insertExternalCaseRecordIntoCaseRecordTable(
			String caseRecordUuid, String externalCaseRecordUuid,
			int externalCaseTypeCode) {
		CaseRecord caseRecord = new CaseRecord();
		Query q = this.getSession().createQuery(
		"from  CaseRecord cr where cr.uuid = ? ");
		q.setString(0, caseRecordUuid);
		caseRecord = (CaseRecord) q.uniqueResult();
		// caseRecord.setExternalCaseRecordUuid(externalCaseRecordUuid);
		// caseRecord.setExternalCaseTypeCode(externalCaseTypeCode);
		this.makePersistent(caseRecord);

	}

	public List<Filing> findAllFilingsByTypeForClerk(int typeCode) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.status.code = 1 " 
			+ " OR c.status.code = 2 " 
			+ " OR c.status.code = 3 " 
			+ " OR c.status.code = 4 " 
			+ " OR c.status.code = 5 " 
			+ " OR c.status.code = 6 " 
			+ " OR c.status.code= 12  " 
			+ " OR c.status.code= 18 ) "  
		+ "AND c.caseType.code = ?"
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;

	}  
	
	public List<Filing> findAllFilingsByTypeForAdmin(int typeCode) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.caseType.code = ?"  
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;

	}  

	
	public List<Filing> findAllFilingsByTypeForJudge(int typeCode) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.status.code = 2 "
			+ " OR  c.status.code = 4 "
			+ " OR  c.status.code = 5 "
			+ " OR c.status.code = 6 "
			+ " OR c.status.code = 13 "
			+ " OR c.status.code= 17 ) " 
		+ "AND c.caseType.code = ?"  
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	}

	public List<Filing> findClearedCaseFilingsAgentSide() {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.status.code = 7 "
			+ " OR  c.status.code = 8) "  
		+ "AND c.clearFilingFlag = true " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);

		}
		return filings;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findClearedCaseFilingsClerkSide()
	 */
	public List<Filing> findClearedCaseFilingsClerkSide() {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.status.code != 7 "
			+ " OR  c.status.code != 8) "  
		+ "AND c.clearFilingFlag = true " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);
		}
		return filings;
	}

	public void deleteCaseRecord(String caseUuid) {			
		this.updateCaseRecordForClearCase(caseUuid,true);
		this.updateCaseRecordForDeletedFlag(caseUuid,true);
	}
	
	public List<Filing> findFilingsByExternalCaseRecordNumber(String externalCaseRecordNumber) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, " 
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, " 
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, " 
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, " 			
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName, "
			+ "crextr.externalCaseRecordNumber "
			+ "FROM CaseRecord c, "
			+  	"CaseRecordStatus crs, "
			+	"LocalCaseType lct, "
			+	"LocalCaseCategory lcc, "
			+	"CaseRecordHistory crh, " 
			+	"CaseRecordEventType cret, "
			+	"PersonParticipants pp, " 
			+	"Person p, " 
			+   "CaseRecordIdExternalCaseRecordNumber crextr "
			+ "WHERE c.clearFilingFlag = false " 
			+ "AND c.deletedFilingFlag = false " 
			+ "AND c.archivedFlag = false " 
			+ "AND c.status.code = crs.code "
			+ "AND c.localCaseType.code = lct.code "
			+ "AND c.localCaseCategory.code = lcc.code "
			+ "AND c.uuid = crh.caseRecord.uuid "
			+ "AND crh.eventType.code=cret.code "
			+ "AND c.uuid = pp.caseRecord.uuid "
			+ "AND pp.relationship.code = 2 "
			+ "AND pp.person.uuid = p.uuid "
			+ "AND crextr.caseRecord.uuid = c.uuid "
			+ "AND crextr.externalCaseRecordNumber = ? "
			+ "AND c.courtUuid = ? "
			+ "GROUP BY crh.caseRecord.uuid "
			+ "ORDER BY crh.eventDateTime DESC ";

		Query q = getSession().createQuery(sql);
		q.setString(0, externalCaseRecordNumber);
		q.setString(1,courtUuid);
		
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			String OCSSCaseNumber = (String) results[9];			
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate, OCSSCaseNumber);
			filings.add(f);

		}
		return filings;
	}
	
	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findNumberOfCaseFilingsByTypeAndStatus(int, int[])
	 */
	public int findNumberOfCaseFilingsByTypeAndStatus(int typeCode, int statusCode1, int statusCode2, int statusCode3, int statusCode4) {

		String sql = "SELECT COUNT(*)FROM caseRecord c WHERE c.clearFilingFlag" +
		" = false and ( c.statusCode = ? or c.statusCode = ? or c.statusCode = ? or c.statusCode = ?) " +
		"and c.caseTypeCode = ? and " +
		"c.deletedFilingFlag = false and c.archivedFlag = false " +
		"AND c.courtUuid = :courtUuid ";
		
		Query q = getSession().createSQLQuery(sql);
		q.setInteger(0, statusCode1);
		q.setInteger(1, statusCode2);
		q.setInteger(2, statusCode3);
		q.setInteger(3, statusCode4);
		q.setInteger(4, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;


	}
	
	/*
	 * (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#sortFiling(int, int, int, int, int, boolean, boolean, java.lang.String)
	 */
	public List<Filing> sortFiling(int caseType, int statusCode1,
			int statusCode2, int statusCode3, int statusCode4, boolean desc,
			boolean orderByEvt, String personalCasesFilter) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String order = "";
		String orderBy = "";
		String personalFilter = "";
		
		if (desc) {
			order = "DESC";
		}
		else {
			order = "ASC";
		}
		
		if (orderByEvt) {

			orderBy = "crh.eventDateTime";
		}
		else {
			orderBy = "c.instantiationDateTime";
		}
		
		if (personalCasesFilter.equalsIgnoreCase("null")) {
			personalFilter = "";
		}
		else {
			personalFilter = "AND ppa.person.uuid = '"
				+ personalCasesFilter + "' ";
		}
		
		if(courtUuid.equalsIgnoreCase("courtUuid4") && (statusCode1 == 0 && statusCode2 == 2 && statusCode3 == 0 && statusCode4 == 0)){
			String sql = "SELECT c.uuid, "
				+ " c.caseTrackingId, "
				+ " c.instantiationDateTime, "
				+ " crs.description, "
				+ " lct.description AS caseTypeDescription, "
				+ " lcc.description, "
				+ " MAX(crh.eventDateTime) AS latestEventTime, "
				+ " cret.description AS latestEventType, "
				+ " CONCAT(p.firstName, ' ', p.lastName) AS defendantName "
				+ " FROM CaseRecord c, "
				+ " CaseRecordStatus crs, "
				+ " LocalCaseType lct, "
				+ " LocalCaseCategory lcc, "
				+ " CaseRecordHistory crh, "
				+ " CaseRecordEventType cret, "
				+ " PersonParticipants pp, "
				+ " PersonParticipants ppa,"
				+ " Person p"
				+ " inner join c.documents docs "
				+ " WHERE (c.status.code = ? " 
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? ) "
				+ " AND c.caseType.code = 1 "
				+ " AND c.clearFilingFlag = 0 "
				+ " AND c.deletedFilingFlag = 0 "
				+ " AND c.archivedFlag = 0 "
				+ " AND c.status.code = crs.code "
				+ " AND c.localCaseType.code = lct.code "
				+ " AND c.localCaseCategory.code = lcc.code "
				+ " AND c.uuid = crh.caseRecord.uuid "
				+ " AND crh.eventType.code = cret.code "
				+ " AND c.uuid = pp.caseRecord.uuid "
				+ " AND pp.relationship.code = 2 "
				+ " AND pp.person.uuid = p.uuid "
				+ " AND ppa.caseRecord.uuid = pp.caseRecord.uuid "
				+ " AND c.courtUuid = :courtUuid "
				+ " AND (docs.documentInstance.code = 707 " 
				+ " OR docs.documentInstance.code = 708 "
				+ " OR docs.documentInstance.code = 709 "
				+ " OR docs.documentInstance.code = 710 "
				+ " OR docs.documentInstance.code = 711 "
				+ " OR docs.documentInstance.code = 712) "
				+ personalFilter
				+ " GROUP BY crh.caseRecord.uuid"
				+ " ORDER BY " + orderBy + " " + order;
			
			Query q = getSession().createQuery(sql);
			q.setInteger(0, statusCode1);
			q.setInteger(1, statusCode2);
			q.setInteger(2, statusCode3);
			q.setInteger(3, statusCode4);
			q.setParameter("courtUuid", courtUuid); 
	
			List l = q.list();
			Iterator i = l.iterator();
			while (i.hasNext()) {
				Object[] results = (Object[]) i.next();
				String uuid = (String) results[0];
				String caseTrackingId = (String) results[1];
				Date instantiationDate = (Date) results[2];
				String status = (String) results[3];
				String typeCode = (String) results[4];
				String caseCategory = (String) results[5];
				Date eventDate = (Date) results[6];
				String eventType = (String) results[7];
				String defendantName = (String) results[8];
	
				Filing f = new Filing(uuid, caseTrackingId, status, typeCode,
						instantiationDate,defendantName,eventType,eventDate);
				
				filings.add(f);
			}
			
			return filings;
		} else {
			String sql = "SELECT c.uuid, "
				+ " c.caseTrackingId, "
				+ " c.instantiationDateTime, "
				+ " crs.description, "
				+ " lct.description AS caseTypeDescription, "
				+ " lcc.description, "
				+ " MAX(crh.eventDateTime) AS latestEventTime, "
				+ " cret.description AS latestEventType, "
				+ " CONCAT(p.firstName, ' ', p.lastName) AS defendantName "
				+ " FROM CaseRecord c, "
				+ " CaseRecordStatus crs, "
				+ " LocalCaseType lct, "
				+ " LocalCaseCategory lcc, "
				+ " CaseRecordHistory crh, "
				+ " CaseRecordEventType cret, "
				+ " PersonParticipants pp, "
				+ " PersonParticipants ppa, "
				+ " Person p "
				+ " WHERE (c.status.code = ? " 
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? ) "
				+ " AND c.caseType.code = 1 "
				+ " AND c.clearFilingFlag = 0 "
				+ " AND c.deletedFilingFlag = 0 "
				+ " AND c.archivedFlag = 0 "
				+ " AND c.status.code = crs.code "
				+ " AND c.localCaseType.code = lct.code "
				+ " AND c.localCaseCategory.code = lcc.code "
				+ " AND c.uuid = crh.caseRecord.uuid "
				+ " AND crh.eventType.code = cret.code "
				+ " AND c.uuid = pp.caseRecord.uuid "
				+ " AND pp.relationship.code = 2 "
				+ " AND pp.person.uuid = p.uuid "
				+ " AND ppa.caseRecord.uuid = pp.caseRecord.uuid "
				+ " AND c.courtUuid = :courtUuid "
				+ personalFilter
				+ " GROUP BY crh.caseRecord.uuid"
				+ " ORDER BY " + orderBy + " " + order;
			
			Query q = getSession().createQuery(sql);
			q.setInteger(0, statusCode1);
			q.setInteger(1, statusCode2);
			q.setInteger(2, statusCode3);
			q.setInteger(3, statusCode4);
			q.setParameter("courtUuid", courtUuid); 
	
			List l = q.list();
			Iterator i = l.iterator();
			while (i.hasNext()) {
				Object[] results = (Object[]) i.next();
				String uuid = (String) results[0];
				String caseTrackingId = (String) results[1];
				Date instantiationDate = (Date) results[2];
				String status = (String) results[3];
				String typeCode = (String) results[4];
				String caseCategory = (String) results[5];
				Date eventDate = (Date) results[6];
				String eventType = (String) results[7];
				String defendantName = (String) results[8];
	
				Filing f = new Filing(uuid, caseTrackingId, status, typeCode,
						instantiationDate,defendantName,eventType,eventDate);
				
				filings.add(f);
			}
			
			return filings;
		}
/*	
		
		return sortFiling(caseType, statusCode1, statusCode2, statusCode3,
				statusCode4, statusCode4, desc, orderByEvt,
				personalCasesFilter);*/
		
	}
	
	/*
	 * (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#sortFiling(int, int, int, int, int, boolean, boolean)
	 */
	public List<Filing> sortFiling(int caseType, int statusCode1, 
			int statusCode2, int statusCode3, int statusCode4, int statusCode5, 
			boolean desc, boolean orderByEvt, String personalCasesFilter) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String order = "";
		String orderBy = "";
		String personalFilter = "";
		
		if (desc) {
			order = "DESC";
		}
		else {
			order = "ASC";
		}
		
		if (orderByEvt) {

			orderBy = "crh.eventDateTime";
		}
		else {
			orderBy = "c.instantiationDateTime";
		}
		
		if (personalCasesFilter.equalsIgnoreCase("null")) {
			personalFilter = "";
		}
		else {
			personalFilter = "AND crh.person.uuid = '"
				+ personalCasesFilter + "' ";
		}
		
		if(courtUuid.equalsIgnoreCase("courtUuid4") && (statusCode1 == 0 && statusCode2 == 2 && statusCode3 == 0 && statusCode4 == 0 && statusCode5 == 0)){
			String sql = "SELECT c.uuid, "
				+ " c.caseTrackingId, "
				+ " c.instantiationDateTime, "
				+ " crs.description, "
				+ " lct.description AS caseTypeDescription, "
				+ " lcc.description, "
				+ " MAX(crh.eventDateTime) AS latestEventTime, "
				+ " cret.description AS latestEventType, "
				+ " CONCAT(p.firstName, ' ', p.lastName) AS defendantName "
				+ " FROM CaseRecord c, "
				+ " CaseRecordStatus crs, "
				+ " LocalCaseType lct, "
				+ " LocalCaseCategory lcc, "
				+ " CaseRecordHistory crh, "
				+ " CaseRecordEventType cret, "
				+ " PersonParticipants pp, "
				+ " Person p "
				+ " inner join c.documents docs "
				+ " WHERE (c.status.code = ? " 
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? ) "
				+ " AND c.caseType.code = 1 "
				+ " AND c.clearFilingFlag = 0 "
				+ " AND c.deletedFilingFlag = 0 "
				+ " AND c.archivedFlag = 0 "
				+ " AND c.status.code = crs.code "
				+ " AND c.localCaseType.code = lct.code "
				+ " AND c.localCaseCategory.code = lcc.code "
				+ " AND c.uuid = crh.caseRecord.uuid "
				+ " AND crh.eventType.code = cret.code "
				+ " AND c.uuid = pp.caseRecord.uuid "
				+ " AND pp.relationship.code = 2 "
				+ " AND pp.person.uuid = p.uuid "
				+ " AND c.courtUuid = :courtUuid "
				+ " AND (docs.documentInstance.code = 707 " 
				+ " OR docs.documentInstance.code = 708 "
				+ " OR docs.documentInstance.code = 709 "
				+ " OR docs.documentInstance.code = 710 "
				+ " OR docs.documentInstance.code = 711 "
				+ " OR docs.documentInstance.code = 712) "
				+ personalFilter
				+ " GROUP BY crh.caseRecord.uuid"
				+ " ORDER BY " + orderBy + " " + order;
			
			Query q = getSession().createQuery(sql);
			q.setInteger(0, statusCode1);
			q.setInteger(1, statusCode2);
			q.setInteger(2, statusCode3);
			q.setInteger(3, statusCode4);
			q.setInteger(4, statusCode5);
			q.setParameter("courtUuid", courtUuid); 
	
			List l = q.list();
			Iterator i = l.iterator();
			while (i.hasNext()) {
				Object[] results = (Object[]) i.next();
				String uuid = (String) results[0];
				String caseTrackingId = (String) results[1];
				Date instantiationDate = (Date) results[2];
				String status = (String) results[3];
				String typeCode = (String) results[4];
				String caseCategory = (String) results[5];
				Date eventDate = (Date) results[6];
				String eventType = (String) results[7];
				String defendantName = (String) results[8];
	
				Filing f = new Filing(uuid, caseTrackingId, status, typeCode,
						instantiationDate,defendantName,eventType,eventDate);
				
				filings.add(f);
			}
			
			return filings;
		} else {
			String sql = "SELECT c.uuid, "
				+ " c.caseTrackingId, "
				+ " c.instantiationDateTime, "
				+ " crs.description, "
				+ " lct.description AS caseTypeDescription, "
				+ " lcc.description, "
				+ " MAX(crh.eventDateTime) AS latestEventTime, "
				+ " cret.description AS latestEventType, "
				+ " CONCAT(p.firstName, ' ', p.lastName) AS defendantName "
				+ " FROM CaseRecord c, "
				+ " CaseRecordStatus crs, "
				+ " LocalCaseType lct, "
				+ " LocalCaseCategory lcc, "
				+ " CaseRecordHistory crh, "
				+ " CaseRecordEventType cret, "
				+ " PersonParticipants pp, "
				+ " Person p "
				+ " WHERE (c.status.code = ? " 
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? "
				+ " OR c.status.code = ? ) "
				+ " AND c.caseType.code = 1 "
				+ " AND c.clearFilingFlag = 0 "
				+ " AND c.deletedFilingFlag = 0 "
				+ " AND c.archivedFlag = 0 "
				+ " AND c.status.code = crs.code "
				+ " AND c.localCaseType.code = lct.code "
				+ " AND c.localCaseCategory.code = lcc.code "
				+ " AND c.uuid = crh.caseRecord.uuid "
				+ " AND crh.eventType.code = cret.code "
				+ " AND c.uuid = pp.caseRecord.uuid "
				+ " AND pp.relationship.code = 2 "
				+ " AND pp.person.uuid = p.uuid "
				+ " AND c.courtUuid = :courtUuid "
				+ personalFilter
				+ " GROUP BY crh.caseRecord.uuid"
				+ " ORDER BY " + orderBy + " " + order;
			
			Query q = getSession().createQuery(sql);
			q.setInteger(0, statusCode1);
			q.setInteger(1, statusCode2);
			q.setInteger(2, statusCode3);
			q.setInteger(3, statusCode4);
			q.setInteger(4, statusCode5);
			q.setParameter("courtUuid", courtUuid); 
	
			List l = q.list();
			Iterator i = l.iterator();
			while (i.hasNext()) {
				Object[] results = (Object[]) i.next();
				String uuid = (String) results[0];
				String caseTrackingId = (String) results[1];
				Date instantiationDate = (Date) results[2];
				String status = (String) results[3];
				String typeCode = (String) results[4];
				String caseCategory = (String) results[5];
				Date eventDate = (Date) results[6];
				String eventType = (String) results[7];
				String defendantName = (String) results[8];
	
				Filing f = new Filing(uuid, caseTrackingId, status, typeCode,
						instantiationDate,defendantName,eventType,eventDate);
				
				filings.add(f);
			}
			
			return filings;
		}
	}
	
	public List<Filing> sortFilingPaged(int caseType, int statusCode1,
			int statusCode2, int statusCode3, int statusCode4, boolean desc,
			boolean orderByEvt, int startRow, String personalCasesFilter,
			int pageSize) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		String order = "";
		String orderBy = "";
		String personalFilter = "";
		
		if (desc) {
			order = "DESC";
		}
		else {
			order = "ASC";
		}
		
		if (orderByEvt) {
			orderBy = "crh.eventDateTime";
		}
		else {
			orderBy = "c.instantiationDateTime";
		}
		
		if (personalCasesFilter.equalsIgnoreCase("null")) {
			personalFilter = "";
		}
		else {
			personalFilter = "AND ppa.person.uuid = '"
				+ personalCasesFilter + "' ";
		}

		String sql = "SELECT c.uuid, "
			+ " c.caseTrackingId, "
			+ " c.instantiationDateTime, "
			+ " crs.description, "
			+ " lct.description AS caseTypeDescription, "
			+ " lcc.description, "
			+ " MAX(crh.eventDateTime) AS latestEventTime, "
			+ " cret.description AS latestEventType, "
			+ " CONCAT(p.firstName, ' ', p.lastName) AS defendantName "
			+ " FROM CaseRecord c, "
			+ " CaseRecordStatus crs, "
			+ " LocalCaseType lct, "
			+ " LocalCaseCategory lcc, "
			+ " CaseRecordHistory crh, "
			+ " CaseRecordEventType cret, "
			+ " PersonParticipants pp, "
			+ " PersonParticipants ppa, "
			+ " Person p "
			+ " WHERE (c.status.code = ? " 
			+ " OR c.status.code = ? "
			+ " OR c.status.code = ? "
			+ " OR c.status.code = ? ) "
			+ " AND c.caseType.code = ? "	
			+ " AND c.caseType.code = 1 "
			+ " AND c.clearFilingFlag = 0 "
			+ " AND c.deletedFilingFlag = 0 "
			+ " AND c.archivedFlag = 0 "
			+ " AND c.status.code = crs.code "
			+ " AND c.localCaseType.code = lct.code "
			+ " AND c.localCaseCategory.code = lcc.code "
			+ " AND c.uuid = crh.caseRecord.uuid "
			+ " AND crh.eventType.code = cret.code "
			+ " AND c.uuid = pp.caseRecord.uuid "
			+ " AND pp.relationship.code = 2 "
			+ " AND pp.person.uuid = p.uuid "
			+ " AND ppa.caseRecord.uuid = pp.caseRecord.uuid "
			+ personalFilter
			+ "AND c.courtUuid = :courtUuid "
			+ " GROUP BY crh.caseRecord.uuid"
			+ " ORDER BY " + orderBy + " " + order;		
		
		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, statusCode1);
		q.setInteger(1, statusCode2);
		q.setInteger(2, statusCode3);
		q.setInteger(3, statusCode4);
		q.setInteger(4, caseType);
		q.setParameter("courtUuid", courtUuid);

		// Set page to query
		q.setFirstResult(startRow);
		
		// Set max results for page
		q.setMaxResults(pageSize);
		
		LOG.debug("query string for sort filing paged: " + q.getQueryString());
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingId = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String typeCode = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			

			Filing f = new Filing(uuid, caseTrackingId, status, typeCode, instantiationDate,defendantName,eventType,eventDate);
			filings.add(f);
		}
	

		return filings;
/*	
		
		return sortFilingPaged(caseType, statusCode1, statusCode2, statusCode3,
				statusCode4, statusCode4, desc, orderByEvt, startRow,
				personalCasesFilter, pageSize);*/
	}
	
	
	/*
	 * (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#sortFilingPaged(int, int, int, int, int, boolean, boolean, int, int)
	 */
	public List<Filing> sortFilingPaged(int caseType, int statusCode1,
			int statusCode2, int statusCode3, int statusCode4, int statusCode5,
			boolean desc, boolean orderByEvt, int startRow,
			String personalCasesFilter,	int pageSize) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		String order = "";
		String orderBy = "";
		String personalFilter = "";
		
		if (desc) {
			order = "DESC";
		}
		else {
			order = "ASC";
		}
		
		if (orderByEvt) {
			orderBy = "crh.eventDateTime";
		}
		else {
			orderBy = "c.instantiationDateTime";
		}
		
		if (personalCasesFilter.equalsIgnoreCase("null")) {
			personalFilter = "";
		}
		else {
			personalFilter = "AND crh.person.uuid = '"
				+ personalCasesFilter + "' ";
		}

		String sql = "SELECT c.uuid, "
			+ " c.caseTrackingId, "
			+ " c.instantiationDateTime, "
			+ " crs.description, "
			+ " lct.description AS caseTypeDescription, "
			+ " lcc.description, "
			+ " MAX(crh.eventDateTime) AS latestEventTime, "
			+ " cret.description AS latestEventType, "
			+ " CONCAT(p.firstName, ' ', p.lastName) AS defendantName "
			+ " FROM CaseRecord c, "
			+ " CaseRecordStatus crs, "
			+ " LocalCaseType lct, "
			+ " LocalCaseCategory lcc, "
			+ " CaseRecordHistory crh, "
			+ " CaseRecordEventType cret, "
			+ " PersonParticipants pp, "
			+ " Person p "
			+ " WHERE (c.status.code = ? " 
			+ " OR c.status.code = ? "
			+ " OR c.status.code = ? "
			+ " OR c.status.code = ? "
			+ " OR c.status.code = ? ) "
			+ " AND c.caseType.code = ? "	
			+ " AND c.caseType.code = 1 "
			+ " AND c.clearFilingFlag = 0 "
			+ " AND c.deletedFilingFlag = 0 "
			+ " AND c.archivedFlag = 0 "
			+ " AND c.status.code = crs.code "
			+ " AND c.localCaseType.code = lct.code "
			+ " AND c.localCaseCategory.code = lcc.code "
			+ " AND c.uuid = crh.caseRecord.uuid "
			+ " AND crh.eventType.code = cret.code "
			+ " AND c.uuid = pp.caseRecord.uuid "
			+ " AND pp.relationship.code = 2 "
			+ " AND pp.person.uuid = p.uuid "
			+ personalFilter
			+ "AND c.courtUuid = :courtUuid "
			+ " GROUP BY crh.caseRecord.uuid"
			+ " ORDER BY " + orderBy + " " + order;		
		
		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, statusCode1);
		q.setInteger(1, statusCode2);
		q.setInteger(2, statusCode3);
		q.setInteger(3, statusCode4);
		q.setInteger(4, statusCode5);
		q.setInteger(5, caseType);
		q.setParameter("courtUuid", courtUuid);

		// Set page to query
		q.setFirstResult(startRow);
		
		// Set max results for page
		q.setMaxResults(pageSize);
		
		LOG.debug("query string for sort filing paged: " + q.getQueryString());
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingId = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String typeCode = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			

			Filing f = new Filing(uuid, caseTrackingId, status, typeCode, instantiationDate,defendantName,eventType,eventDate);
			filings.add(f);
		}
	

		return filings;
	}
	
	

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findCaseFilingsByTypeAndStatus(int, int, int, int)
	 */
	public List<Filing> findCaseFilingsByTypeAndStatus(int typeCode, int statusCode1, int statusCode2, int statusCode3, int statusCode4) {
		ArrayList<Filing> filings = new ArrayList<Filing>();

			String sql = "SELECT c.uuid, "
				+ "c.caseTrackingId, "
				+ "c.instantiationDateTime, " 
				+ "crs.description, "
				+ "lct.description as caseTypeDescription, " 
				+ "lcc.description, "
				+ "MAX(crh.eventDateTime) as latestEventTime, " 
				+ "cret.description AS latesteventType, "
				+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
			+ "FROM CaseRecord c, "
			+  	"CaseRecordStatus crs, "
			+	"LocalCaseType lct, "
			+	"LocalCaseCategory lcc, "
			+	"CaseRecordHistory crh, "
			+	"CaseRecordEventType cret, "
			+	"PersonParticipants pp, "
			+	"Person p "
			+ "WHERE (c.status.code = ? " 
			+ "OR c.status.code = ? "
			+ "OR c.status.code = ? "
			+ "OR c.status.code = ? ) "
			+ "AND c.caseType.code = ? "	
			+ "AND c.clearFilingFlag = false " 
			+ "AND c.deletedFilingFlag = false " 
			+ "AND c.archivedFlag = false "
			+ "AND c.status.code = crs.code "
			+ "AND c.localCaseType.code = lct.code "
			+ "AND c.localCaseCategory.code = lcc.code "
			+ "AND c.uuid = crh.caseRecord.uuid "
			+ "AND crh.eventType.code = cret.code "
			+ "AND c.uuid = pp.caseRecord.uuid "
			+ "AND pp.relationship.code = 2 "
			+ "AND pp.person.uuid = p.uuid "
			+ "AND c.courtUuid = :courtUuid "
			+ "GROUP BY crh.caseRecord.uuid "
			+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setInteger(0, statusCode1);
		q.setInteger(1, statusCode2);
		q.setInteger(2, statusCode3);
		q.setInteger(3, statusCode4);
		q.setInteger(4, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingId = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];

			Filing f = new Filing(uuid, caseTrackingId, status, caseType, instantiationDate,defendantName,eventType,eventDate);
			filings.add(f);
		}
		return filings;

	}
	

	/* 
	 * This is to support outdated calls to the person search.  This sets
	 *	the defendantSearch to false, so the search acts exactly as
	 *	originally designed.
	 */
	public List<Filing> findCaseFilingsByPerson(String firstName,
			String middleName, String lastName) {		
		
		return findCaseFilingsByPerson(firstName, middleName, lastName, false);
	}

	//Added by Frank

	public List<Filing> findCaseFilingsByPerson(String fn, String mn, String ln, boolean defendantSearch) {
		String firstName = fn.trim();
		String middleName= mn.trim();
		String lastName = ln.trim();
		String defendantFilter;
		
		if(defendantSearch) {
			defendantFilter = " AND pp.relationship = 2 ";
		}
		else {
			defendantFilter = "";
		}
		
		LOG.debug("the first name is "+firstName);
		LOG.debug("the middle name is "+middleName);
		LOG.debug("the last name is "+lastName);
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		
		//check if first name is valid
		NameValidator validateFirstName = new NameValidator();
		boolean isFirstNameValid = validateFirstName.isFirstOrLastNameValid(firstName);
		
		//check if middle name is valid
		NameValidator validateMiddleName = new NameValidator();
		boolean isMiddleNameValid = validateMiddleName.isMiddleNameValid(middleName);
		
//		check if last name is valid
		NameValidator validateLastName = new NameValidator();
		boolean isLastNameValid = validateLastName.isFirstOrLastNameValid(lastName);
		
	    if (isFirstNameValid && isMiddleNameValid && isLastNameValid) {
	    	LOG.debug("THERE IS MATCH ");

			// Have the full name
			if ((firstName != "") && (middleName != "") && (lastName != "")) {				
				
				String sql = "SELECT c.uuid, "
					+ "c.caseTrackingId, "
					+ "c.instantiationDateTime, " 
					+ "crs.description, "
					+ "lct.description as caseTypeDescription, " 
					+ "lcc.description, "
					+ "MAX(crh.eventDateTime) as latestEventTime, " 
					+ "cret.description AS latesteventType, "
					+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
				+ "FROM CaseRecord c, "
				+  	"CaseRecordStatus crs, "
				+	"LocalCaseType lct, "
				+	"LocalCaseCategory lcc, "
				+	"CaseRecordHistory crh, "
				+	"CaseRecordEventType cret, "
				+	"PersonParticipants pp, "
				+	"Person p "
				+ "WHERE (p.firstName = ? "
						+ "AND p.middleName = ? "
						+ "AND p.lastName = ? )"
				+ "AND c.clearFilingFlag = false " 
				+ "AND c.deletedFilingFlag = false " 
				+ "AND c.archivedFlag = false "
				+ "AND c.status.code = crs.code "
				+ "AND c.localCaseType.code = lct.code "
				+ "AND c.localCaseCategory.code = lcc.code "
				+ "AND c.uuid = crh.caseRecord.uuid "
				+ "AND crh.eventType.code = cret.code "
				+ "AND c.uuid = pp.caseRecord.uuid "
				+ "AND pp.person.uuid = p.uuid "
				+ defendantFilter
				+ "AND c.courtUuid = :courtUuid "
				+ "GROUP BY crh.caseRecord.uuid "
				+ "ORDER BY crh.eventDateTime DESC";
			
				Query q = getSession().createQuery(sql);
				q.setParameter("courtUuid", courtUuid);
				
				q.setString(0, firstName);
				q.setString(1, middleName);
				q.setString(2, lastName);
				List l = q.list();
				LOG.debug("The returned list of filings is of size "+l.size());
				Iterator i = l.iterator();
				while (i.hasNext()) {
					Object[] results = (Object[]) i.next();

					String uuid = (String) results[0];
					String caseTrackingID = (String) results[1];
					Date instantiationDate = (Date) results[2];
					String status = (String) results[3];
					String caseType = (String) results[4];
					String caseCategory = (String) results[5];
					Date eventDate = (Date) results[6];
					String eventType = (String) results[7];
					String defendantName = (String) results[8];
					Filing f = new Filing(uuid, caseTrackingID, status,
							caseType, instantiationDate, defendantName,
							eventType, eventDate);
					filings.add(f);
				}
			}

			// Only have first name
			if ((firstName != "") && (middleName == "") && (lastName == "")) {
				LOG.debug("Only have first name");
				
				String sql = "SELECT c.uuid, "
					+ "c.caseTrackingId, "
					+ "c.instantiationDateTime, " 
					+ "crs.description, "
					+ "lct.description as caseTypeDescription, " 
					+ "lcc.description, "
					+ "MAX(crh.eventDateTime) as latestEventTime, " 
					+ "cret.description AS latesteventType, "
					+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
				+ "FROM CaseRecord c, "
				+  	"CaseRecordStatus crs, "
				+	"LocalCaseType lct, "
				+	"LocalCaseCategory lcc, "
				+	"CaseRecordHistory crh, "
				+	"CaseRecordEventType cret, "
				+	"PersonParticipants pp, "
				+	"Person p "
				+ "WHERE p.firstName = ? "
				+ "AND c.clearFilingFlag = false " 
				+ "AND c.deletedFilingFlag = false " 
				+ "AND c.archivedFlag = false "
				+ "AND c.status.code = crs.code "
				+ "AND c.localCaseType.code = lct.code "
				+ "AND c.localCaseCategory.code = lcc.code "
				+ "AND c.uuid = crh.caseRecord.uuid "
				+ "AND crh.eventType.code = cret.code "
				+ "AND c.uuid = pp.caseRecord.uuid "
				+ "AND pp.person.uuid = p.uuid "
				+ defendantFilter
				+ "AND c.courtUuid = :courtUuid "
				+ "GROUP BY crh.caseRecord.uuid "
				+ "ORDER BY crh.eventDateTime DESC";
			
				Query q = getSession().createQuery(sql);
				q.setString(0, firstName);
				q.setParameter("courtUuid", courtUuid);
				
				List l = q.list();
				Iterator i = l.iterator();
				while (i.hasNext()) {
					Object[] results = (Object[]) i.next();

					String uuid = (String) results[0];
					String caseTrackingID = (String) results[1];
					Date instantiationDate = (Date) results[2];
					String status = (String) results[3];
					String caseType = (String) results[4];
					String caseCategory = (String) results[5];
					Date eventDate = (Date) results[6];
					String eventType = (String) results[7];
					String defendantName = (String) results[8];
					Filing f = new Filing(uuid, caseTrackingID, status,
							caseType, instantiationDate, defendantName,
							eventType, eventDate);
					filings.add(f);

				}
			}
			
			// Only have the last name
			if ((firstName == "") && (middleName == "") && (lastName != "")) {
				LOG.debug("Only have last name");
				
				String sql = "SELECT c.uuid, "
					+ "c.caseTrackingId, "
					+ "c.instantiationDateTime, " 
					+ "crs.description, "
					+ "lct.description as caseTypeDescription, " 
					+ "lcc.description, "
					+ "MAX(crh.eventDateTime) as latestEventTime, " 
					+ "cret.description AS latesteventType, "
					+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
				+ "FROM CaseRecord c, "
				+  	"CaseRecordStatus crs, "
				+	"LocalCaseType lct, "
				+	"LocalCaseCategory lcc, "
				+	"CaseRecordHistory crh, "
				+	"CaseRecordEventType cret, "
				+	"PersonParticipants pp, "
				+	"Person p "
				+ "WHERE p.lastName = ? "
				+ "AND c.clearFilingFlag = false " 
				+ "AND c.deletedFilingFlag = false " 
				+ "AND c.archivedFlag = false "
				+ "AND c.status.code = crs.code "
				+ "AND c.localCaseType.code = lct.code "
				+ "AND c.localCaseCategory.code = lcc.code "
				+ "AND c.uuid = crh.caseRecord.uuid "
				+ "AND crh.eventType.code = cret.code "
				+ "AND c.uuid = pp.caseRecord.uuid "
				+ "AND pp.person.uuid = p.uuid "
				+ defendantFilter
				+ "AND c.courtUuid = :courtUuid "
				+ "GROUP BY crh.caseRecord.uuid "
				+ "ORDER BY crh.eventDateTime DESC";
			
				Query q = getSession().createQuery(sql);
				q.setString(0, lastName);
				q.setParameter("courtUuid", courtUuid);
				
				List l = q.list();
				Iterator i = l.iterator();
				while (i.hasNext()) {
					Object[] results = (Object[]) i.next();

					String uuid = (String) results[0];
					String caseTrackingID = (String) results[1];
					Date instantiationDate = (Date) results[2];
					String status = (String) results[3];
					String caseType = (String) results[4];
					String caseCategory = (String) results[5];
					Date eventDate = (Date) results[6];
					String eventType = (String) results[7];
					String defendantName = (String) results[8];
					Filing f = new Filing(uuid, caseTrackingID, status,
							caseType, instantiationDate, defendantName,
							eventType, eventDate);
					filings.add(f);

				}
			}
			
			// Have first name and last name
			if ((firstName != "") && (middleName == "") && (lastName != "")) {
				LOG.debug("Have first name and last name");
				
				String sql = "SELECT c.uuid, "
					+ "c.caseTrackingId, "
					+ "c.instantiationDateTime, " 
					+ "crs.description, "
					+ "lct.description as caseTypeDescription, " 
					+ "lcc.description, "
					+ "MAX(crh.eventDateTime) as latestEventTime, " 
					+ "cret.description AS latesteventType, "
					+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
				+ "FROM CaseRecord c, "
				+  	"CaseRecordStatus crs, "
				+	"LocalCaseType lct, "
				+	"LocalCaseCategory lcc, "
				+	"CaseRecordHistory crh, "
				+	"CaseRecordEventType cret, "
				+	"PersonParticipants pp, "
				+	"Person p "
				+ "WHERE (p.firstName = ? "
						+ "AND p.lastName = ? )"
				+ "AND c.clearFilingFlag = false " 
				+ "AND c.deletedFilingFlag = false " 
				+ "AND c.archivedFlag = false "
				+ "AND c.status.code = crs.code "
				+ "AND c.localCaseType.code = lct.code "
				+ "AND c.localCaseCategory.code = lcc.code "
				+ "AND c.uuid = crh.caseRecord.uuid "
				+ "AND crh.eventType.code = cret.code "
				+ "AND c.uuid = pp.caseRecord.uuid "
				+ "AND pp.person.uuid = p.uuid "
				+ defendantFilter
				+ "AND c.courtUuid = :courtUuid "
				+ "GROUP BY crh.caseRecord.uuid "
				+ "ORDER BY crh.eventDateTime DESC";
			
				Query q = getSession().createQuery(sql);
				q.setString(0, firstName);
				q.setString(1, lastName);
				q.setParameter("courtUuid", courtUuid);
				
				List l = q.list();
				Iterator i = l.iterator();
				while (i.hasNext()) {
					Object[] results = (Object[]) i.next();

					String uuid = (String) results[0];
					String caseTrackingID = (String) results[1];
					Date instantiationDate = (Date) results[2];
					String status = (String) results[3];
					String caseType = (String) results[4];
					String caseCategory = (String) results[5];
					Date eventDate = (Date) results[6];
					String eventType = (String) results[7];
					String defendantName = (String) results[8];
					Filing f = new Filing(uuid, caseTrackingID, status,
							caseType, instantiationDate, defendantName,
							eventType, eventDate);
					filings.add(f);

				}
			}
		}
	    LOG.debug("The size of the returned filings is "+filings.size());
		return filings;
	}

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findNumberOfCaseFilingsByType(int)
	 */
	public int findNumberOfCaseFilingsByType(int typeCode) {
		String sql = "SELECT COUNT(*)FROM caseRecord c WHERE c.clearFilingFlag" +
		" = false and  c.caseTypeCode = ? and " +
		"c.deletedFilingFlag = false and c.archivedFlag = false "
		+ "AND c.courtUuid = :courtUuid ";  
		Query q = getSession().createSQLQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();

		return resultSetSize;
	}
	
	

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findCaseFilingsByTypeAndStatus(int, int, int)
	 */
	public List<Filing> findCaseFilingsByTypeAndStatus(int typeCode, int startRow, int pageSize) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.caseType.code = ? "	
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);

		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		// Set page to query
		q.setFirstResult(startRow);
		
		// Set max results for page
		q.setMaxResults(pageSize);
		
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];

			Filing f = new Filing(uuid, caseTrackingID, status, caseType, instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);
		}
		return filings;
	}

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findCaseFilingsByTypeAndStatusPaged(int, int, int, int, int, int)
	 */
	public List<Filing> findCaseFilingsByTypeAndStatusPaged(int typeCode, int status1, int status2, int status3, int status4, int startRow, int pageSize) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
				+ "c.caseTrackingId, "
				+ "c.instantiationDateTime, " 
				+ "crs.description, "
				+ "lct.description as caseTypeDescription, " 
				+ "lcc.description, "
				+ "MAX(crh.eventDateTime) as latestEventTime, " 
				+ "cret.description AS latesteventType, "
				+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
			+ "FROM CaseRecord c, "
			+  	"CaseRecordStatus crs, "
			+	"LocalCaseType lct, "
			+	"LocalCaseCategory lcc, "
			+	"CaseRecordHistory crh, "
			+	"CaseRecordEventType cret, "
			+	"PersonParticipants pp, "
			+	"Person p "
			+ "WHERE (c.status.code = ? " 
			+ "OR c.status.code = ? "
			+ "OR c.status.code = ? "
			+ "OR c.status.code = ? ) "
			+ "AND c.caseType.code = ? "	
			+ "AND c.clearFilingFlag = false " 
			+ "AND c.deletedFilingFlag = false " 
			+ "AND c.archivedFlag = false "
			+ "AND c.status.code = crs.code "
			+ "AND c.localCaseType.code = lct.code "
			+ "AND c.localCaseCategory.code = lcc.code "
			+ "AND c.uuid = crh.caseRecord.uuid "
			+ "AND crh.eventType.code = cret.code "
			+ "AND c.uuid = pp.caseRecord.uuid "
			+ "AND pp.relationship.code = 2 "
			+ "AND pp.person.uuid = p.uuid "
			+ "AND c.courtUuid = :courtUuid "
			+ "GROUP BY crh.caseRecord.uuid "
			+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setInteger(0, status1);
		q.setInteger(1, status2);
		q.setInteger(2, status3);
		q.setInteger(3, status4);
		q.setInteger(4, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		// Set page to query
		q.setFirstResult(startRow);
		
		// Set max results for page
		q.setMaxResults(pageSize);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];

			Filing f = new Filing(uuid, caseTrackingID, status, caseType, instantiationDate,defendantName,eventType,eventDate);
			filings.add(f);
		}
		return filings;

	} 
	//added by Michael Alexandrou
	public List<Filing> findFilingByCaseTrackingId(String caseTrackingId) {
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE c.caseTrackingId = ? "
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = ? "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";
	
		Query q = getSession().createQuery(sql);
		q.setString(0, caseTrackingId);
		q.setString(1,courtUuid);
		
		List<Object[]> results = q.list();
		List<Filing> fList = new java.util.LinkedList(); 
		
		for(Object[] result : results)
		{
			if(result!=null){
				LOG.debug("the size of the query is .."+result.length);
				String uuid = (String) result[0];
				String trackingId = (String) result[1];
				Date instantiationDate = (Date) result[2];
				String status = (String) result[3];
				String caseType = (String) result[4];
				String caseCategory = (String) result[5];
				Date eventDate = (Date) result[6];
				String eventType = (String) result[7];
				String defendantName = (String) result[8];
				Filing f = new Filing(uuid, trackingId, status, caseType, 
						instantiationDate, defendantName, eventType, eventDate);
				fList.add(f);			
			}
		}
		return fList;
	}

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findAllFilingsByTypeForClerkPaged(int, int, int)
	 */
	public List<Filing> findAllFilingsByTypeForClerkPaged(int typeCode, int startRow, int pageSize) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE " 
		+ " c.caseType.code = ? "
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";
	
		Query q = getSession().createQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		q.setFirstResult(startRow );
		
		// Set max results for page
		q.setMaxResults(pageSize);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];

			Filing f = new Filing(uuid, caseTrackingID, status, caseType, 
					instantiationDate,defendantName,eventType,eventDate);
			filings.add(f);

		}
		return filings;
	}
	
	/**
	 * Method to determine how many rows are possible to be returned by a query 
	 * for case filings that are cleared but not deleted and not archived
	 * 
	 * @param typeCode
	 * @return int Count of number of rows
	 */
	public int findNumberOfClearedFilingsByType(int typeCode ) {
		String sql = "SELECT COUNT(*)FROM caseRecord c WHERE c.clearFilingFlag" +
		" = true and c.caseTypeCode = ? and " +
		"c.deletedFilingFlag = false and c.archivedFlag = false" + 
		" AND c.courtUuid = :courtUuid ";  
		Query q = getSession().createSQLQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();

		return resultSetSize;
	} 
	
	
	/**
	 * @param typeCode
	 * @return the filings that are flagged as cleared
	 * One possible use of this method is from the admin module where an authorized administrator
	 * sees all the cleared filings and decides to restore one or more of them
	 */
	public List<Filing> findAllClearedFilings(int typeCode){

		ArrayList<Filing> filings = new ArrayList<Filing>();
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.caseType.code = ? )"
		+ "AND c.clearFilingFlag = true " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";
	
		Query q = getSession().createQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];

			Filing f = new Filing(uuid, caseTrackingID, status, caseType, 
					instantiationDate,defendantName,eventType,eventDate);
			filings.add(f);

		}
		return filings;
	
	}

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findAllFilingsByTypeForJudgePaged(int, int, int)
	 */
	public List<Filing> findAllFilingsByTypeForJudgePaged(int typeCode, int startRow, int pageSize) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.status.code = 2 " 
			+ "OR c.status.code = 4 " 
			+ "OR c.status.code = 5 " 
			+ "OR c.status.code = 6 "
			+ "OR c.status.code = 13 "
			+ "OR c.status.code = 17) " 
		+ "AND c.caseType.code = ? "
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";
	
		Query q = getSession().createQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		q.setFirstResult(startRow );
		
		// Set max results for page
		q.setMaxResults(pageSize);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];

			Filing f = new Filing(uuid, caseTrackingID, status, caseType, 
					instantiationDate,defendantName,eventType,eventDate);
			filings.add(f);

		}
		return filings;
	}

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findNumberOfCaseFilingsByTypeForClerk(int)
	 */
	public int findNumberOfCaseFilingsByTypeForClerk(int typeCode) {
		
		 String sql = "SELECT COUNT(*) FROM caseRecord c WHERE c.clearFilingFlag" +
		" = false and  c.caseTypeCode = ? and " +
		"c.deletedFilingFlag = false and c.archivedFlag = false " +
		"and (c.statusCode = 1 " +
		" or c.statusCode = 2 " +
		" or c.statusCode = 3 " +
		" or c.statusCode = 4 " +
		" or c.statusCode = 5 " +
		" or c.statusCode = 6 " +
		" or c.statusCode= 12  " +
		" or c.statusCode= 18 ) "
		+ "AND c.courtUuid = :courtUuid ";  
		
		Query q = getSession().createSQLQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#findNumberOfCaseFilingsByTypeForJudge(int)
	 */
	public int findNumberOfCaseFilingsByTypeForJudge(int typeCode) {
		String sql = "SELECT COUNT(*)FROM caseRecord c WHERE c.clearFilingFlag" +
		" = false and  c.caseTypeCode = ? and " +
		"c.deletedFilingFlag = false and c.archivedFlag = false " +
		" and (c.statusCode = 2 " +
		" or  c.statusCode = 4 " +
		" or  c.statusCode = 5 " +
		" or c.statusCode = 6 " +
		" or c.statusCode = 13 " +
		" or c.statusCode= 17 ) "
		+ "AND c.courtUuid = :courtUuid ";
		
		Query q = getSession().createSQLQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}

	public int findNumberOfCaseFilingsByTypeForAdmin(int typeCode) {
		String sql = "SELECT COUNT(*)FROM caseRecord c WHERE c.clearFilingFlag" +
		" = false and  c.caseTypeCode = ? and " +
		"c.deletedFilingFlag = false and c.archivedFlag = false "
		+ "AND c.courtUuid = :courtUuid ";
		
		Query q = getSession().createSQLQuery(sql);
		q.setInteger(0, typeCode);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}

	public List<Filing> findCaseFilingsForPastDays(Date startingDate, Date endingDate, String personLoginUuid) {
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE crh.eventDateTime >= ? "
		+ "AND crh.eventDateTime <= ? "
		+ "AND (cret.code = 9 "
		+ "OR cret.code = 1) "	
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND crh.person.uuid = ? "
		+ "AND c.courtUuid = :courtUuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		q.setTimestamp(0, startingDate);
		q.setTimestamp(1, endingDate);
		q.setString(2, personLoginUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String defendantName = (String) results[8];
			Filing f = new Filing(uuid, caseTrackingID, status, caseType,
					instantiationDate, defendantName, eventType, eventDate);
			filings.add(f);
		}
		return filings;
	}

	/* (non-Javadoc)
	 * @see us.gaaoc.framework.dao.CaseRecordDAO#createCaseRecord(java.util.Date, java.util.Date, us.gaaoc.framework.model.CaseRecordStatus, us.gaaoc.framework.model.CaseRecordType, us.gaaoc.framework.model.Court, us.gaaoc.framework.model.LocalCaseCategory, us.gaaoc.framework.model.LocalCaseDivision, us.gaaoc.framework.model.LocalCaseFilingType, us.gaaoc.framework.model.LocalCaseType)
	 */
	public CaseRecord createCaseRecord(Date instanciationDate,
			Date submittedDate, CaseRecordStatus crSt, CaseRecordType crT,
			Court c, LocalCaseCategory localCaseCategory,
			LocalCaseDivision localCaseDivision,
			LocalCaseFilingType localCaseFilingType, LocalCaseType localCaseType) {
		
		CaseRecord caseRecord = new CaseRecord();
		caseRecord.setInstantiationDateTime(instanciationDate);
		caseRecord.setSubmittedDateTime(submittedDate);
		caseRecord.setStatus(crSt);
		caseRecord.setCaseType(crT);
		caseRecord.setCourt(c);
		caseRecord.setLocalCaseCategory(localCaseCategory);
		caseRecord.setLocalCaseDivision(localCaseDivision);
		caseRecord.setLocalCaseFilingType(localCaseFilingType);
		caseRecord.setLocalCaseType(localCaseType);
		
		return caseRecord;
	}
	
	//TODO Update Query to pull case record uuid's for a particular court date
	
	public List<String> findCaseFilingsWithinDateRange(Date startingDate, Date endingDate) {
		
		String sql = "SELECT caseRecordUuid " +
				"FROM courtEvents " +
				"WHERE eventDateTime > ? " +
				"AND eventDateTime < ? "
				+ "AND c.courtUuid = :courtUuid ";

		Query q = getSession().createSQLQuery(sql);
		q.setTimestamp(0, startingDate);
		q.setTimestamp(1, endingDate);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		Iterator i = l.iterator();
		ArrayList<String> caseRecordUuids = new ArrayList<String>();
		while (i.hasNext()) {
			String uuid = (String) i.next();
			caseRecordUuids.add(uuid);
			
		}
		return caseRecordUuids;
	}
	/**
	 * change the status of a case record
	 * @param caseReocrd
	 * @param caseRecordStatusCode
	 */
	public void UpdateCaseRecordByStatus(CaseRecord caseRecord, CaseRecordStatus caseRecordStatus){
		CaseRecord c = this.findById(caseRecord.getUuid());
		c.setStatus(caseRecordStatus);
		this.makePersistent(c);
	
	}
	
	/**
	 *  update case filing by the clear filing flag
	 *  
	 *   
	 *  @param caseRecordUuid
	 *  @param clearFilingFlag
	 */
	public void UpdateFilingByClearFilingFlag( int typeCode, String caseRecordUuid, boolean clearFilingFlag) {
		CaseRecord caseRecord = this.findById(caseRecordUuid);
		caseRecord.setClearFilingFlag(clearFilingFlag);
		this.makePersistent(caseRecord);
		
		//TODO: everything from this line and below does nothing, I believe.
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"Person p "
		+ "WHERE (c.caseType.code = ? )"
		+ "AND c.uuid = ? "
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";
	
		Query q = getSession().createQuery(sql);
		q.setInteger(0, typeCode);
		q.setString(1, caseRecordUuid);
		Object [] l = (Object [])q.uniqueResult();

		String uuid = (String)l[0];
		String caseTrackingID = (String)l[1];
		Date instantiationDate = (Date)l[2];
		String status = (String)l[3];
		String caseType = (String)l[4];
		String caseCategory = (String)l[5];
		Date eventDate = (Date)l[6];
		String eventType = (String)l[7];
		String defendantName = (String)l[8];

		Filing f = new Filing(uuid, caseTrackingID, status, caseType, 
				instantiationDate,defendantName,eventType,eventDate);
	}
	
	   // methods added by Bhawana
	public int countCaseRecords() {
		String sql = "SELECT COUNT(uuid) FROM caseRecord WHERE deletedFilingFlag = 0 AND clearFilingFlag = 0 AND courtUuid = :courtUuid ";
		Query q = getSession().createSQLQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;

		
	}

	public int countContemptCases() {
		String sql="SELECT COUNT(uuid) FROM caseRecord WHERE deletedFilingFlag = 0 AND clearFilingFlag = 0 AND localCaseCategoryCode=118 AND courtUuid = :courtUuid";
		Query q = getSession().createSQLQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
		
	}
	
	public int countEstablishedCases(){
		String sql="SELECT COUNT(uuid) FROM caseRecord WHERE deletedFilingFlag = 0 AND clearFilingFlag = 0 AND localCaseCategoryCode=20 AND courtUuid = :courtUuid";
		Query q = getSession().createSQLQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}
	public int countLocalCases(){
		String sql = "SELECT COUNT(uuid)" + "FROM caseRecord cr, "
				+ "localCaseCategory lcc "
				+ "	WHERE cr.localCaseCategoryCode = lcc.code "
				+ "	AND deletedFilingFlag = 0 " + "	AND clearFilingFlag = 0 "
				+ "	GROUP BY lcc.code AND cr.courtUuid = :courtUuid";
		Query q = getSession().createSQLQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}
	public List<CaseRecordsCountPerMonth> countCasesByMonth() {
		String sql = "SELECT MONTHNAME(instantiationDateTime) AS Date, "
				+ "   COUNT(uuid) " + "FROM caseRecord cr "
				+ "   WHERE deletedFilingFlag = 0 "
				+ "  AND clearFilingFlag = 0 " + "GROUP BY date AND courtUuid = :courtUuid";
		Query q = getSession().createSQLQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l =  q.list();
		List <CaseRecordsCountPerMonth> monthList=new ArrayList<CaseRecordsCountPerMonth>();
		CaseRecordsCountPerMonth caseRecordsCountPerMonth=new  CaseRecordsCountPerMonth();

		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			byte[]  monthName = (byte[]) results[0];
			BigInteger tempCount = (BigInteger) results[1];
			String value=new String(monthName);
			int count = tempCount.intValue();
			CaseRecordsCountPerMonth m=new CaseRecordsCountPerMonth(value,count);
			monthList.add(m);
		}
		return monthList;
	}
	public List<CaseRecordsCountPerDay> countCasesByDay(){
		String sql="SELECT  COUNT(uuid),DAYNAME(instantiationDateTime) AS Date " +
            " FROM caseRecord cr " +
            " WHERE courtUuid = :courtUuid" +
            " GROUP BY date";
    Query q = getSession().createSQLQuery(sql);
    q.setParameter("courtUuid", courtUuid);
    
	List l =  q.list();
	List<CaseRecordsCountPerDay> caseList = new ArrayList<CaseRecordsCountPerDay>();
	CaseRecordsCountPerDay caseRecordsCountPerDay = new CaseRecordsCountPerDay();
	Iterator i = l.iterator();
	while (i.hasNext()) {
		Object[] results = (Object[]) i.next();
		BigInteger tempCount = (BigInteger) results[0];
		byte[]  dayName = (byte[]) results[1];
		String value= new String(dayName);
		int count = tempCount.intValue();
        CaseRecordsCountPerDay c = new CaseRecordsCountPerDay( value, count);
	    caseList.add(c);
	}
    return  caseList;
}
	
	
 //Filing Statistics
	public int countFilings() {
		String sql="SELECT count(*) FROM caseRecord cr, caseRecordHistory crh where cr.uuid = crh.caseRecordUuid and cr.courtUuid = :courtUuid ORDER BY eventDateTime LIMIT 10;";
		Query q = getSession().createSQLQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}

	public int countProcessedFiling() {
		String sql="SELECT COUNT(uuid) FROM caseRecord WHERE statusCode = 2 OR statusCode = 6 OR statusCode =  12 AND courtUuid = :courtUuid";
		Query q = getSession().createSQLQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}


	public int findNumberOfAllCaseRecordsByCaseTypeAndStatus(int typeCode, int statusCode1, int statusCode2) {
		String sql = "SELECT COUNT(*)FROM caseRecord c WHERE c.clearFilingFlag" +
		" = false and  c.caseTypeCode = ? and " +
		"(c.statusCode = ? or c.statusCode = ?) and " +
		"c.deletedFilingFlag = false and c.archivedFlag = false AND c.courtUuid = :courtUuid";
		Query q = getSession().createSQLQuery(sql);
		q.setInteger(0, typeCode);
		q.setInteger(1, statusCode1);
		q.setInteger(2, statusCode2);
		q.setParameter("courtUuid", courtUuid);
		
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}
	
   //Dismissed Cases
	public List<DismissedCases> findDismissedCases() {
	
		String sql = "SELECT  COUNT(c.uuid), " +
				"c.caseTrackingID ," +
				"ecrcr.externalCaseRecordNumber, " +
				"crs.description, " +
				"CONCAT(p.firstName, ' ', p.lastName) AS defendantName, " +
				"c.instantiationDateTime, d.title, ds.description , di.description " +
				"FROM caseRecord c, " +
				"documents d, " +
				"documentInstance di," +
				"person p, participantRelationship pr, " +
				"personParticipants pp , " +
				"caseRecordIdExternalCaseRecordNumber ecrcr, " +
				"caseRecordStatus crs, documentStatus ds  " +
				" where c.uuid=d.caseRecordUuid " +
				"and c.clearFilingFlag=0 " +
				" and c.deletedFilingFlag=0 " +
				"and d.documentInstanceCode= di.code " +
				"and  di.docTypeCode=45  " +
				"and p.uuid= pp.personUuid " +
				"and  pp.relationshipCode=pr.code  " +
				"and ecrcr.caseRecordUuid = c.uuid " +
				"and pp.caseRecordUuid=c.uuid " +
				"and pr.code=2  and c.statusCode = crs.code and d.statusCode = ds.code " +
				"AND c.courtUuid = :courtUuid " + 
				"GROUP BY c.instantiationDateTime ";
		
        Query q = getSession().createSQLQuery(sql);
        q.setParameter("courtUuid", courtUuid);
        
		List l = q.list();
		List<DismissedCases> docList = new ArrayList<DismissedCases>();
	
		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();

			BigInteger tempCount = (BigInteger) results[0];
			int count2 = tempCount.intValue();
			String caseTrackingID = (String) results[1];
			String externalCaseRecordNumber2 = (String) results[2];
			String status2 = (String) results[3];
			String defendantName2 = (String) results[4];
			Date instantiationDate2 = (Date) results[5];
			String title=(String) results[6];
			String docStatus2= (String)results[7];
			String documentInstanceCode2=(String) results[8];
			
			DismissedCases d=new DismissedCases(count2,caseTrackingID,externalCaseRecordNumber2,status2,defendantName2,instantiationDate2,title,docStatus2,documentInstanceCode2);
			docList.add(d);
		}
		return docList;
}


	public List<DismissedCasesPerMonth> countDismissedCasesPerMonth() {
		String sql="SELECT MONTHNAME(instantiationDateTime) AS Date,  COUNT(c.uuid) " +
				"FROM caseRecord c ,documents d , documentInstance di " +
				" WHERE deletedFilingFlag = 0 " +
				" AND clearFilingFlag = 0 " +
				" AND d.documentInstanceCode = di.code and (di.docTypeCode=45 or di.docTypeCode=10) AND c.courtUuid = :courtUuid" +
				" GROUP BY date ";


		Query q = getSession().createSQLQuery(sql);
		q.setParameter("courtUuid", courtUuid);
		
		List l =  q.list();
		List <DismissedCasesPerMonth> monthList=new ArrayList<DismissedCasesPerMonth>();
		DismissedCasesPerMonth dismissedCasesPerMonth=new  DismissedCasesPerMonth();

		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			byte[]  monthName = (byte[]) results[0];
			BigInteger tempCount = (BigInteger) results[1];
			String value=new String(monthName);
			int count = tempCount.intValue();
			DismissedCasesPerMonth d=new DismissedCasesPerMonth(value,count);
			monthList.add(d);
		}
		return monthList;
	}
	
	/**
	 * This method is used for Fulton and the web service counties. It generates the CV number for the case record. 
	 */
	public String generateDocketId(String prefix) {
		String hql = "SELECT c.caseTrackingId from CaseRecord c where c.caseTrackingId like :prefix order by c.caseTrackingId DESC";

		String docketId = "";
		String oldDocketId = "";

		oldDocketId = (String) getSession().createQuery(hql).setParameter(
				"prefix", prefix + "%").setMaxResults(1).uniqueResult();

		LOG.debug("oldDocketId = " + oldDocketId + ".");

		if (oldDocketId != null) {
			oldDocketId = (oldDocketId.substring(6, 11)).trim();
			int oldFileID = Integer.parseInt(oldDocketId);
			oldFileID++;
			char[] idArray = Integer.toString(oldFileID).toCharArray();
			switch (idArray.length) {
			case 4:
				docketId = prefix + "0" + Integer.toString(oldFileID);
				break;
			case 3:
				docketId = prefix + "00" + Integer.toString(oldFileID);
				break;
			case 2:
				docketId = prefix + "000" + Integer.toString(oldFileID);
				break;
			case 1:
				docketId = prefix + "0000" + Integer.toString(oldFileID);
				break;
			}
		} else {
			docketId = prefix + "00001";
		}
		
		return docketId;
	}

	public void updateCaseRecord(CaseRecord caseRecord) {
		this.getSession().save(caseRecord);
	}

	public boolean findCivilActionNumber(String cvNumber) {
		Query q = this
				.getSession()
				.createQuery(
						"from CaseRecord where caseTrackingId = :id and courtUuid = :courtUuid")
				.setParameter("id", cvNumber).setParameter("courtUuid",
						courtUuid);
		
		// If the list is empty, then there is not a case with that number. That means that its ok to add one.
		// If the list has something, then there is a case and its not cool to add a new one.
		if(q.list().isEmpty())
			return false;
		else
			return true;
	}
	
	
	/**
	 * Report Summary Page Records
	 */
	
public List<Filing> findAllCaseRecordByStatusandCreatedDate(int statusCode,String courtUuid,Date startInstantiationDateTime,Date endInstantiationDateTime) {
		
		/**
		System.out.println("*******************************************************************************************************");
		System.out.println("In the Method which queries the Database by just the Status Code the Case  and selected Created Dates");
		System.out.println("*******************************************************************************************************");
*/
		
		ArrayList<Filing> filings = new ArrayList<Filing>();
		
		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "crecr.externalCaseRecordNumber AS OCSSCaseNumber, " 
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"CaseRecordIdExternalCaseRecordNumber crecr, "
		+	"Person p "
		+ "WHERE c.status.code = ? "
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND c.uuid = crecr.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid " 
		+ "and c.instantiationDateTime between :fromDate and :toDate "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, statusCode);
		q.setParameter("courtUuid", courtUuid);
		q.setParameter("fromDate", startInstantiationDateTime);
		q.setParameter("toDate", endInstantiationDateTime);
		List list = q.list();
		Iterator i = list.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String OCSSCaseNumber=(String) results[8];
			String defendantName = (String) results[9];

			//Filing f = new Filing(uuid, caseTrackingID, status, caseType, instantiationDate,defendantName,eventType,eventDate);
			Filing f = new Filing(uuid,caseTrackingID,status,caseType,instantiationDate,defendantName,eventType,eventDate,OCSSCaseNumber);

			filings.add(f);
		}
		return filings;

	}
	
	
	
	public List<Filing> findAllCaseRecordByStatusCodeandLatestEventDateTime(int statusCode,String courtUuid,Date startEventDateTime,Date endEventDateTime)
	{
		/**
		System.out.println("*******************************************************************************************************");
		System.out.println("In the Method which queries the Database by just the Status Code the Case is in and  between the selected Dates by latest event Dates");
		System.out.println("*******************************************************************************************************");
*/

		ArrayList<Filing> filings = new ArrayList<Filing>();
		

		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, "
			+ "crecr.externalCaseRecordNumber AS OCSSCaseNumber, " 
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"CaseRecordIdExternalCaseRecordNumber crecr, "
		+	"Person p "
		+ "WHERE c.status.code = ? "
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND c.uuid = crecr.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid " 
		+ "GROUP BY crh.caseRecord.uuid " 
		+ "HAVING MAX(crh.eventDateTime) between :fromDate and :toDate "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, statusCode);
		q.setParameter("courtUuid", courtUuid);
		q.setParameter("fromDate", startEventDateTime);
		q.setParameter("toDate", endEventDateTime);
		List list = q.list();
		Iterator i = list.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String OCSSCaseNumber=(String) results[8];
			String defendantName = (String) results[9];

			//Filing f = new Filing(uuid, caseTrackingID, status, caseType, instantiationDate,defendantName,eventType,eventDate);
			Filing f = new Filing(uuid,caseTrackingID,status,caseType,instantiationDate,defendantName,eventType,eventDate,OCSSCaseNumber);

			filings.add(f);
		}
		return filings;

	
	}
	
	
	public List<Filing> findAllCaseRecordByEventCodeandLatestEventDateTime(int eventCode,String courtUuid,Date startEventDateTime,Date endEventDateTime)
	{
		/**
		System.out.println("*******************************************************************************************************");
		System.out.println("In the Method which queries the Database by just the Event Code(Latest Case Action) that was made between the selected Dates by latest event Dates");
		System.out.println("*******************************************************************************************************");
*/

		ArrayList<Filing> filings = new ArrayList<Filing>();
		

		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, " 
			+ "crecr.externalCaseRecordNumber AS OCSSCaseNumber, " 
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"CaseRecordIdExternalCaseRecordNumber crecr, "
		+	"Person p "
		+ "WHERE cret.code = ? "
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND c.uuid = crecr.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid " 
		+ "GROUP BY crh.caseRecord.uuid " 
		+ "HAVING MAX(crh.eventDateTime) between :fromDate and :toDate "
		+ "ORDER BY crh.eventDateTime DESC";

		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, eventCode);
		q.setParameter("courtUuid", courtUuid);
		q.setParameter("fromDate", startEventDateTime);
		q.setParameter("toDate", endEventDateTime);
		List list = q.list();
		Iterator i = list.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String OCSSCaseNumber=(String) results[8];
			String defendantName = (String) results[9];

			//Filing f = new Filing(uuid, caseTrackingID, status, caseType, instantiationDate,defendantName,eventType,eventDate);
			Filing f = new Filing(uuid,caseTrackingID,status,caseType,instantiationDate,defendantName,eventType,eventDate,OCSSCaseNumber);

			filings.add(f);
		}
		return filings;

	
	}
	
	
	
	public List<Filing> findAllCaseRecordByEventCodeandEventDateTime(int eventCode,String courtUuid,Date startEventDateTime,Date endEventDateTime)
	{
		/**
		System.out.println("*******************************************************************************************************");
		System.out.println("In the Method which queries the Database by just the Event Code(Case Action) that was made between the selected Dates");
		System.out.println("*******************************************************************************************************");
*/

		ArrayList<Filing> filings = new ArrayList<Filing>();
		

		String sql = "SELECT c.uuid, "
			+ "c.caseTrackingId, "
			+ "c.instantiationDateTime, " 
			+ "crs.description, "
			+ "lct.description as caseTypeDescription, " 
			+ "lcc.description, "
			+ "MAX(crh.eventDateTime) as latestEventTime, " 
			+ "cret.description AS latesteventType, " 
			+ "crecr.externalCaseRecordNumber AS OCSSCaseNumber, " 
			+ "CONCAT(p.firstName,' ', p.lastName) AS defendantName "
		+ "FROM CaseRecord c, "
		+  	"CaseRecordStatus crs, "
		+	"LocalCaseType lct, "
		+	"LocalCaseCategory lcc, "
		+	"CaseRecordHistory crh, "
		+	"CaseRecordEventType cret, "
		+	"PersonParticipants pp, "
		+	"CaseRecordIdExternalCaseRecordNumber crecr, "
		+	"Person p "
		+ "WHERE cret.code = ? "
		+ "AND c.clearFilingFlag = false " 
		+ "AND c.deletedFilingFlag = false " 
		+ "AND c.archivedFlag = false "
		+ "AND c.status.code = crs.code "
		+ "AND c.localCaseType.code = lct.code "
		+ "AND c.localCaseCategory.code = lcc.code "
		+ "AND c.uuid = crh.caseRecord.uuid "
		+ "AND c.uuid = crecr.caseRecord.uuid "
		+ "AND crh.eventType.code = cret.code "
		+ "AND c.uuid = pp.caseRecord.uuid "
		+ "AND pp.relationship.code = 2 "
		+ "AND pp.person.uuid = p.uuid "
		+ "AND c.courtUuid = :courtUuid " 
		+ "AND crh.eventDateTime between :fromDate and :toDate "
		+ "GROUP BY crh.caseRecord.uuid "
		+ "ORDER BY crh.eventDateTime DESC";


		Query q = getSession().createQuery(sql);
		
		q.setInteger(0, eventCode);
		q.setParameter("courtUuid", courtUuid);
		q.setParameter("fromDate", startEventDateTime);
		q.setParameter("toDate", endEventDateTime);
		List list = q.list();
		Iterator i = list.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			
			String uuid = (String) results[0];
			String caseTrackingID = (String) results[1];
			Date instantiationDate = (Date) results[2];
			String status = (String) results[3];
			String caseType = (String) results[4];
			String caseCategory = (String) results[5];
			Date eventDate = (Date) results[6];
			String eventType = (String) results[7];
			String OCSSCaseNumber=(String) results[8];
			String defendantName = (String) results[9];

			//Filing f = new Filing(uuid, caseTrackingID, status, caseType, instantiationDate,defendantName,eventType,eventDate);
			Filing f = new Filing(uuid,caseTrackingID,status,caseType,instantiationDate,defendantName,eventType,eventDate,OCSSCaseNumber);

			filings.add(f);
		}
		return filings;

	
	
	}
}